﻿Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports Microsoft.Win32
Imports System.Xml
Imports System.IO

Public Class AsistenteConexion
    Dim _RutaResp As String = Vales_Verificador_3.Verificador._RutaResp
    Dim cs As String = "" 'used to store the sql connection string
    Dim osb As String = "" 'used to store the oledb connection string
    Dim dbname As String = "" 'used o store the name of the database
    Dim noPassword As Boolean = False
    Dim goodCon As Boolean = False

    'Public Class SQLHelpers
    Public Shared Function GetServerList(ByVal cmbServers As ComboBox)
        Dim Server As String = String.Empty
        Dim instance As Sql.SqlDataSourceEnumerator = Sql.SqlDataSourceEnumerator.Instance
        Dim table As System.Data.DataTable = instance.GetDataSources()
        For Each row As System.Data.DataRow In table.Rows
            Server = String.Empty
            Server = row("ServerName")
            If row("InstanceName").ToString.Length > 0 Then
                Server = Server & "\" & row("InstanceName")
            End If
            cmbServers.Items.Add(Server)
        Next
        cmbServers.SelectedIndex = cmbServers.FindStringExact(Environment.MachineName)
        Return ""
    End Function

    Private Function GetSQLServerList() As String()
        Dim dse As SqlDataSourceEnumerator = SqlDataSourceEnumerator.Instance
        Dim dt As DataTable = dse.GetDataSources()
        If dt.Rows.Count = 0 Then
            Return Nothing
        End If

        Dim SQLServers As String() = New String(dt.Rows.Count - 1) {}
        Dim f As Integer = -1
        For Each r As DataRow In dt.Rows
            Dim SQLServer As String = r("ServerName").ToString()
            Dim Instance As String = r("InstanceName").ToString()
            If Instance IsNot Nothing AndAlso Instance <> "" Then
                SQLServer += "\" + Instance
            End If
            SQLServers(System.Math.Max(System.Threading.Interlocked.Increment(f), f - 1)) = SQLServer
        Next
        Array.Sort(SQLServers)
        Return SQLServers
    End Function

    ''' 
    ''' Returns a list of user databases on the specified server instance 
    ''' 
    ''' Name of server and if applicable the instance name 
    ''' DEV10\SQLEXPRESS 
    ''' MYSQLSERVER 
    ''' 
    ''' true if SSPI should be used; otherwise the username and password must be specified. 
    ''' username of an SQL server user account 
    ''' password for the SQL account specified by the username above 
    ''' fixed string array containing the list of user databases 
    Private Function GetSQLDatabaseList(ByVal serverInstanceName As String, ByVal useWindowsAuthentication As Boolean, ByVal username As String, ByVal password As String) As String()
        Dim csb As New SqlConnectionStringBuilder()
        csb.DataSource = serverInstanceName 'cmbServers.Text 'cboSrcDB.Text
        csb.IntegratedSecurity = useWindowsAuthentication
        'csb.TrustServerCertificate = useWindowsAuthentication
        csb.InitialCatalog = "master"
        If Not useWindowsAuthentication Then
            csb.UserID = username
            csb.Password = password
        End If


        Dim conn As New SqlConnection(csb.ToString())


        ' The 'where name like 'f%_' will filter out just those databases begining with "F" or "f"
        Dim da As New SqlDataAdapter("Select name from sysdatabases ", conn)
        'removed the following from above query so that all databases would be shown
        '  where name like 'f%_'
        Try
            Dim dt As New DataTable("Databases")
            Dim rowsAffected As Integer = da.Fill(dt)
            If dt Is Nothing OrElse rowsAffected <= 0 Then
                Return Nothing
            End If

            Dim f As Integer = -1
            Dim databases As String() = New String(dt.Rows.Count - 1) {}
            For Each r As DataRow In dt.Rows
                databases(System.Math.Max(System.Threading.Interlocked.Increment(f), f - 1)) = r("name").ToString()
            Next
            da.Dispose()
            Array.Sort(databases)
            Return databases
        Catch ex As SqlException
            MessageBox.Show("Cannot obtain database list:" & vbLf + ex.Message, "Migration Tool", MessageBoxButtons.OK, MessageBoxIcon.[Error])
            Return Nothing
        Finally
            conn.Close()
        End Try
    End Function

    Private Sub BuildConnection()
        Dim csb As New SqlConnectionStringBuilder

        If chkUseWindowsSecurity.Checked Then
            csb.DataSource = cmbServers.Text
            csb.IntegratedSecurity = chkUseWindowsSecurity.Checked
            csb.InitialCatalog = cmbDatabases.Text
            cs = csb.ToString

            osb = "Provider = SQLOLEDB.1;Integrated Security = SSPI;Persist Security Info = False; Initial Catalog = " & cmbDatabases.Text & ";Data Source = " & cmbServers.Text
            dbname = cmbDatabases.Text

        Else
            csb.DataSource = cmbServers.Text
            csb.IntegratedSecurity = chkUseWindowsSecurity.Checked
            csb.InitialCatalog = cmbDatabases.Text
            csb.UserID = txtUserName.Text
            csb.Password = txtPassword.Text
            cs = csb.ToString

            osb = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID= " & txtUserName.Text & ";Initial Catalog= " & cmbDatabases.Text & ";Data Source = " & cmbServers.Text

            dbname = cmbDatabases.Text
        End If
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'we'll get a list of servers before the wizard loads so as to avoid the end user
        'thinking that nothing is happening
        Try
            Me.cmbServers.Items.Clear()
            GetServerList(Me.cmbServers)
            'cmbServers.Items.AddRange(GetSQLServerList())
        Catch ex As Exception
            Dim mymsg As String = "Ocurrio un problema Buscando los servidores, ingrese el servidor manualmente."
            MessageBox.Show(mymsg, "Error retrieving SQL Server Instances", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Try

        'we want to diable the browse for servers button if we have a list of servers
        'If cmbServers.Items.Count <> 0 Then
        '    btnFindServers.Enabled = False
        'Else
        '    btnFindServers.Enabled = True
        'End If
    End Sub

    Private Sub cmbServers_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbServers.SelectedValueChanged
        If chkUseWindowsSecurity.Checked Then
            cmbDatabases.Items.AddRange(GetSQLDatabaseList(cmbServers.Text, True, txtUserName.Text, txtPassword.Text))
            'now disable the find databses button if we have a list
            If cmbDatabases.Items.Count <> 0 Then
                btnFindDatabases.Enabled = False
            Else
                btnFindDatabases.Enabled = True
            End If
        Else
        End If
    End Sub

    Private Sub btnFindServers_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFindServers.Click
        ''it's possible to duplicate the list so
        'If cmbServers.Items.Count <> 0 Then
        'Else
        '    cmbServers.Items.AddRange(GetSQLServerList())
        'End If
        Me.cmbServers.Items.Clear()
        GetServerList(Me.cmbServers)
    End Sub

    Private Sub chkUseWindowsSecurity_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chkUseWindowsSecurity.CheckedChanged
        If chkUseWindowsSecurity.Checked Then
            txtUserName.Enabled = False
            txtPassword.Enabled = False
            chkBlankPassAllowed.Enabled = False
        Else
            txtUserName.Enabled = True
            txtPassword.Enabled = True
            chkBlankPassAllowed.Enabled = True
        End If
    End Sub

    Private Sub chkUseWindowsSecurity_CheckStateChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkUseWindowsSecurity.CheckStateChanged
        'by default the button is checked if this is fire then the user intends using user authentification
        'firstly reinstate the enabled state of the browse for databases button if its disabled
        If btnFindDatabases.Enabled = False Then
            btnFindDatabases.Enabled = True
        End If
    End Sub

    Private Sub cmbDatabases_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbDatabases.Click
        If chkUseWindowsSecurity.Checked = False Then
            If chkBlankPassAllowed.Checked = False Then


                If txtUserName.Text = "" Then
                    Dim msg As String = "You have unchecked the 'Use Windows Security' checkbox." _
                    & ControlChars.CrLf _
                    & "Consequently you must enter a user name"
                    MessageBox.Show(msg, "No User Name", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    txtUserName.Focus()
                    Exit Sub

                ElseIf txtPassword.Text = "" Then
                    Dim msg As String = "A password is required." & ControlChars.CrLf & "If you know the password is blank, check the 'Blank Password Allowed' box."

                    MessageBox.Show(msg, "Result", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    'don't worry no password was deliberate.
                    'it's possible to get caught in a loop here so add a check to say cancel has been pressed


                    txtPassword.Focus()
                    Exit Sub
                End If
            Else
                If txtUserName.Text = "" Then
                    Dim msg As String = "You have unchecked the 'Use Windows Security' checkbox." _
                    & ControlChars.CrLf _
                    & "Consequently you must enter a user name"
                    MessageBox.Show(msg, "Result", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    txtUserName.Focus()
                    Exit Sub
                End If
            End If

            'we have info so process it
            Try

                cmbDatabases.Items.AddRange(GetSQLDatabaseList(cmbServers.Text, False, txtUserName.Text, txtPassword.Text))


            Catch ex As Exception
                MessageBox.Show("There has been an error trying to connect to the database with the parameters you entered.\n" + ex.Message, "Result", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End Try
        Else

        End If

    End Sub

    Private Sub btnFindDatabases_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFindDatabases.Click
        'it's possible to duplicate the list so we need to ensure that there isn't one already
        If cmbDatabases.Items.Count <> 0 Then
        Else
            If chkUseWindowsSecurity.Checked Then
                'we just need to do a check with no details
                cmbDatabases.Items.AddRange(GetSQLDatabaseList(cmbServers.Text, True, txtUserName.Text, txtPassword.Text))
            Else
                'we need to do a check with details
                cmbDatabases.Items.AddRange(GetSQLDatabaseList(cmbServers.Text, False, txtUserName.Text, txtPassword.Text))

            End If
        End If
    End Sub

    Private Sub btnTestConnection_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnTestConnection.Click
        BuildConnection()
        Dim con As New SqlConnection(cs)
        Try
            con.Open()
            MessageBox.Show("Conexion establecida", "Result", MessageBoxButtons.OK, MessageBoxIcon.Information)
            goodCon = True
        Catch ex As Exception
            MessageBox.Show("Conexion fallida\n" + ex.Message, "Result", MessageBoxButtons.OK, MessageBoxIcon.Warning)
        End Try


    End Sub

    Private Sub btnFinish_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFinish.Click
        Dim _RutaReportes = Vales_Verificador_3.Verificador._RutaReportes
        If cs <> "" Then
            'we have a connection string
            If goodCon = True Then
                'it was validated so we can go ahead and save it

                'firstly as xml

                'Dim fname As String = Path.GetDirectoryName(My.Computer.FileSystem.SpecialDirectories.CurrentUserApplicationData) & "\ConnectionStrings.xml"
                'Dim writer As XmlTextWriter = New XmlTextWriter(fname, Nothing)
                'writer.Formatting = Formatting.Indented
                'WriteConnectionStrings(writer, cs, osb)
                'writer.Close()
                My.Settings.Save()
                My.Settings.RunTimeSqlConnectionString = "Server=" & cmbServers.Text & ";Database=admingas;User Id=sa;Password=admingas;"
                My.Settings.Save()
                My.Settings.Reload()
                Dim ini As New IniFile
                ini.AddSection("instancia").AddKey("instancia").Value = Me.cmbServers.Text
                ini.Save(Verificador._RutaConexion)
                DialogResult = Windows.Forms.DialogResult.OK
                My.Settings.Save()
                Me.Close()
                'In order to use the new connection string you have just created the application needsto close.
                'When you restart the application you should load the connection string from the xml file.
                'Application.Exit()
            Else
                'connection wasn't validated so just close
                DialogResult = Windows.Forms.DialogResult.OK
                Me.Close()

            End If
        End If
    End Sub

    Public Sub WriteConnectionStrings(ByVal writer As XmlWriter, ByVal sqlcon As String, ByVal oledbcon As String)
        writer.WriteStartElement("ConnectionStrings")
        writer.WriteElementString("SqlConnectionString", sqlcon)
        writer.WriteElementString("DatabaseName", dbname)
        writer.WriteEndElement()
    End Sub

    Private Sub cmbDatabases_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbDatabases.SelectedIndexChanged

    End Sub
End Class
